<?php

include_once('top.php');

$employees = fetch_all("SELECT ALL.First , ALL.Last, ALL.Email ,
OPEN.Count AS Open, CLOSED.Count AS Closed ,
SUM(TOTAL.Count) AS Total , HOURS.Hours
FROM (
SELECT First_name AS First , Last_name AS Last, Email
FROM USER WHERE `Type`='R'
) AS `ALL` LEFT JOIN (
SELECT USER.First_name AS First , USER.Last_name
AS Last, USER.Email AS Email , TASK.Open
AS Open, COUNT(TASK.Name) AS Count
FROM (USER LEFT JOIN TASK
ON USER.Email = TASK.Employee)
WHERE Open=1 OR Open IS NULL
GROUP BY USER.Email
) AS OPEN ON ALL.Email = OPEN.Email LEFT JOIN (
SELECT USER.First_name AS First , USER.Last_name
AS Last, USER.Email AS Email , TASK.Open
AS Open, COUNT(TASK.Name) AS Count
FROM (USER LEFT JOIN TASK
ON USER.Email = TASK.Employee)
WHERE Open=0 OR Open IS NULL
GROUP BY USER.Email
) AS CLOSED ON OPEN.Email = CLOSED.Email LEFT JOIN (
SELECT USER.First_name AS First , USER.Last_name
AS Last, USER.Email AS Email , TASK.Open
AS Open, COUNT(TASK.Name) AS Count
FROM (USER LEFT JOIN TASK
ON USER.Email = TASK.Employee)
GROUP BY USER.Email
) AS TOTAL ON OPEN.Email = TOTAL.Email LEFT JOIN (
SELECT User, SUM(Hours) AS Hours
FROM ENTRY
GROUP BY User
) AS HOURS ON OPEN.Email = HOURS.User
GROUP BY TOTAL.Email");

$totalOpen = 0;
$totalClosed = 0;
$totalTasks = 0;
$totalHours = 0;

for ($i = 0; $i < count($employees); $i++) {
	if (empty($employees[$i]['Closed'])) {
		$employees[$i]['Closed'] = 0;
	}
	
	if (empty($employees[$i]['Total'])) {
		$employees[$i]['Total'] = 0;
	}
	
	if (empty($employees[$i]['Hours'])) {
		$employees[$i]['Hours'] = 0;
	}
}

?>

<html>
<head>
	<title>GT Project Manager - Employee Tasks Report</title>
	<link rel="stylesheet" type="text/css" href="main.css" />
	<link rel="stylesheet" type="text/css" href="inner.css" />
	<style type="text/css">
		.total td {
			border-top: 1px solid black;
			font-weight: bold;
		}
	</style>
</head>
<body>
	<table cellspacing="0" cellpadding="0" id="main">
		<tr><td id="title">
			Employee Tasks Report
			<div class="nav">
				<a href="login.php?action=logout">Log Out</a><br />
				<?php echo $link ?>
			</div>
		</td></tr>
		<tr>
			<td>
				<table class="subsection summary" cellspacing="0" cellpadding="0">
					<tr>
						<th>Employee Name</th>
						<th>Open Tasks</th>
						<th>Closed Tasks</th>
						<th>Total Tasks</th>
						<th>Total Hours</th>
					</tr>
					<?php
					for ($i = 0; $i < count($employees); $i++) {
					echo "
					<tr>
						<td>" . $employees[$i]['First'] . " " . $employees[$i]['Last'] . "</td>
						<td>" . $employees[$i]['Open'] . "</td>
						<td>" . $employees[$i]['Closed'] . "</td>
						<td>" . $employees[$i]['Total'] . "</td>
						<td>" . $employees[$i]['Hours'] . "</td>
					</tr>";
					$totalOpen += $employees[$i]['Open'];
					$totalClosed += $employees[$i]['Closed'];
					$totalTasks += $employees[$i]['Total'];
					$totalHours += $employees[$i]['Hours'];
					};
					?>
					<tr class="total">
						<td>Totals</td><?php
						echo "
						<td>" . $totalOpen . "</td>
						<td>" . $totalClosed . "</td>
						<td>" . $totalTasks . "</td>
						<td>" . $totalHours . "</td>";?>
					</tr>
				</table>
			</td>
		</tr>
	</table>
</body>
</html>
